Imports Microsoft.VisualBasic
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports SubSonic
Imports System.Data
Imports ClubStarterKit.Data

Namespace ClubStarterKit.Web
    Public Class Events
        Public Shared Sub Delete(ByVal Id As String)
            Dim eventqry As New Query(Tables.ClubEvent)
            eventqry.QueryType = QueryType.Delete
            eventqry.AddWhere(ClubEvent.Columns.ID, Id)
            eventqry.Execute()

            Dim attendanceqry As New Query(Tables.Attendance)
            attendanceqry.QueryType = QueryType.Delete
            attendanceqry.AddWhere(Attendance.Columns.Eventid, Id)
            attendanceqry.Execute()
        End Sub

        Public Shared Sub Update(ByVal Id As String, _
            ByVal Description As String, ByVal Endtime As String, _
            ByVal Location As String, ByVal Photo As String, _
            ByVal RSVP As String, ByVal Starttime As String, ByVal staticurl As String, _
            ByVal ttl As String, ByVal TotalAttendace As String, ByVal TLocationDir As String)

            Dim TLocation As String = ""
            If Location = "" Or IsDBNull(Location) = True Then
                TLocation = "0"
            Else
                TLocation = Location
            End If

            Dim TPhoto As String = ""
            If Photo = "" Or IsDBNull(Photo) = True Then
                TPhoto = "0"
            Else
                TPhoto = Photo
            End If

            Dim qry As New Query(Tables.ClubEvent)
            qry.AddWhere(ClubEvent.Columns.ID, Id)
            qry.QueryType = QueryType.Update
            qry.AddUpdateSetting(ClubEvent.Columns.Description, Description)
            qry.AddUpdateSetting(ClubEvent.Columns.Endtime, Endtime)
            qry.AddUpdateSetting(ClubEvent.Columns.LocationName, TLocation)
            qry.AddUpdateSetting(ClubEvent.Columns.LocationDirections, TLocationDir)
            qry.AddUpdateSetting(ClubEvent.Columns.Photo, TPhoto)
            qry.AddUpdateSetting(ClubEvent.Columns.RSVP, RSVP)
            qry.AddUpdateSetting(ClubEvent.Columns.Starttime, Starttime)
            qry.AddUpdateSetting(ClubEvent.Columns.StaticURL, staticurl)
            qry.AddUpdateSetting(ClubEvent.Columns.Title, ttl)
            qry.AddUpdateSetting(ClubEvent.Columns.TotalAttendance, TotalAttendace)

            qry.Execute()
        End Sub

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, True)> _
        Public Shared Function SelectForUpdate(ByVal EventID As String) As IDataReader
            Dim rdr As IDataReader
            Dim qry As New Query(Tables.ClubEvent)
            qry.AddWhere(ClubEvent.Columns.Id, EventID)
            qry.QueryType = QueryType.Select
            qry.Top = "1"
            rdr = qry.ExecuteReader()
            Return rdr
            rdr.Close()
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
        Public Shared Function SelectForCalendar() As IDataReader
            'NOTE: NOT IN USE AT THE MOMENT!!!!
            Dim rdr As IDataReader
            Dim qry As New Query(Tables.ClubEvent)
            qry.QueryType = QueryType.Select
            qry.SelectList = ClubEvent.Columns.Id & "," & ClubEvent.Columns.Starttime & "," & ClubEvent.Columns.Title & "," & ClubEvent.Columns.Description & "," & ClubEvent.Columns.Endtime
            rdr = qry.ExecuteReader()
            Return rdr
            rdr.Close()
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Delete, False)> _
        Public Shared Sub DeleteDownload(ByVal ID As String)
            Dim qry As New Query(Tables.DownloadsInEvents)
            qry.AddWhere(DownloadsInEvents.Columns.Id, ID)
            qry.QueryType = QueryType.Delete
            qry.Execute()
        End Sub

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
        Public Shared Function DownloadsByEventSP(ByVal EventID As String) As IDataReader
            Dim rdr As IDataReader
            rdr = SPs.DownloadsByEvent(EventID).GetReader()
            Return rdr
            rdr.Close()
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
        Public Shared Function EventTitleByIDSP(ByVal eventid As String) As String
            Dim str As String = ""
            Dim eventa As New ClubEvent(eventid)
            If eventa.IsLoaded = True Then
                str = eventa.Title
            Else
                str = ""
            End If
            Return str
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
        Public Shared Function IDandTitle() As IDataReader
            Dim qry As New Query(Tables.Downloads)
            qry.QueryType = QueryType.Select
            qry.SelectList = Downloads.Columns.Id & "," & Downloads.Columns.Title
            Dim rdr As IDataReader = qry.ExecuteReader()
            Return rdr
            rdr.Close()
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Insert, False)> _
        Public Shared Sub AddDownloadToEvent(ByVal eventid As String, ByVal downloadid As String)
            DownloadsInEvents.Insert(eventid, downloadid)
        End Sub

        Public Shared Function NextEvent(ByVal id As Integer) As String
            Dim str As String = ""

            Dim clubevent1 As New ClubEvent(id)
            Dim clubeventtime As String = clubevent1.Starttime


            Dim qry As New Query(Tables.ClubEvent)
            qry.Top = "1" 
            qry.OrderBy = OrderBy.Asc(ClubEvent.Columns.Starttime)
            qry.AddWhere(ClubEvent.Columns.Starttime, Comparison.GreaterThan, clubeventtime)
            Dim rdr As IDataReader = qry.ExecuteReader()

            Dim NextID As Int32
            While rdr.Read
                NextID = rdr.GetInt32(0)
            End While
            rdr.Close()

            If Not IsDBNull(NextID) AndAlso Not NextID = 0 Then
                str = NextID.ToString()
            Else
                str = "-"
            End If
            Return str
        End Function

        Public Shared Function PrevEvent(ByVal id As Integer) As String
            Dim str As String = ""

            Dim clubevent1 As New ClubEvent(id)
            Dim clubeventtime As String = clubevent1.Starttime


            Dim qry As New Query(Tables.ClubEvent)
            qry.Top = "1"
            qry.OrderBy = OrderBy.Desc(ClubEvent.Columns.Starttime)
            qry.AddWhere(ClubEvent.Columns.Starttime, Comparison.LessThan, clubeventtime)
            Dim rdr As IDataReader = qry.ExecuteReader()

            Dim PrevID As Int32
            While rdr.Read
                PrevID = rdr.GetInt32(0)
            End While
            rdr.Close()

            If Not IsDBNull(PrevID) AndAlso Not PrevID = 0 Then
                str = PrevID.ToString()
            Else
                str = "-"
            End If
            Return str
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
        Public Shared Function OpenRSVP(ByVal EventID As Integer) As IDataReader
            Dim qry As String = "SELECT aspnet_Users.UserName, aspnet_Users.UserId, Attendance.numofguest, ClubEvent.CurrentAttendance, ClubEvent.TotalAttendance FROM Attendance INNER JOIN aspnet_Users ON Attendance.member = aspnet_Users.UserId INNER JOIN aspnet_MemberShip ON Attendance.member = aspnet_MemberShip.UserId INNER JOIN ClubEvent ON Attendance.eventid = ClubEvent.id WHERE (Attendance.eventid ="
            qry += CStr(EventID)
            qry += ")"

            Dim rdr As IDataReader
            Dim cmd As New SubSonic.QueryCommand(qry)
            rdr = SubSonic.DataService.GetReader(cmd)
            Return rdr
            rdr.Close()
        End Function

        Public Shared Sub RSVPSanityCheck(ByVal EventID As Integer)
            Dim attnqry As New Query(Tables.Attendance)
            Dim where As New SubSonic.Where
            where.ColumnName = Attendance.Columns.Eventid
            where.ParameterValue = EventID
            Dim sum As Integer = attnqry.GetCount(Attendance.Columns.Numofguest, where)

            Dim upqry As New Query(Tables.ClubEvent)
            upqry.AddWhere(ClubEvent.Columns.ID, EventID)
            upqry.AddUpdateSetting(ClubEvent.Columns.CurrentAttendance, sum)
            upqry.QueryType = QueryType.Update
            upqry.Execute()
        End Sub

        Public Shared Function RowCount(ByVal con As SubSonic.Comparison) As Integer
            Dim qry As New Query(Tables.ClubEvent)
            qry.AddWhere(ClubEvent.Columns.Starttime, con, DateTime.Now())
            Return qry.GetRecordCount()
        End Function

        Public Shared Function NumPages(ByVal IsGreater As Boolean) As Integer
            Dim Comparison As SubSonic.Comparison
            If IsGreater = True Then
                Comparison = SubSonic.Comparison.GreaterThan
            Else
                Comparison = SubSonic.Comparison.LessThan
            End If
            Dim count As Integer = RowCount(Comparison)

            Dim pagesize As Integer = ClubStarterKit.Web.Settings.PageSize()

            Dim dbl As Double = count / pagesize
            Dim rounded As Double = Math.Ceiling(dbl)
            Dim testint As Integer = Integer.Parse(rounded)

            If testint > 0 Then
                Return testint
            Else
                If count > 0 Then
                    Return 1
                Else
                    Return 0
                End If
            End If
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
    Public Shared Function UpcomingList(ByVal pagenum As Integer) As IDataReader
            Dim qry As New Query(Tables.ClubEvent)
            qry.AddWhere(ClubEvent.Columns.Starttime, Comparison.GreaterThan, DateTime.Now())
            qry.PageSize = ClubStarterKit.Web.Settings.PageSize()
            qry.PageIndex = pagenum
            qry.OrderBy = OrderBy.Asc(ClubEvent.Columns.Starttime)
            Dim rdr As IDataReader = qry.ExecuteReader()
            Return rdr
            rdr.Close()
        End Function

        <System.ComponentModel.DataObjectMethodAttribute _
        (System.ComponentModel.DataObjectMethodType.Select, False)> _
    Public Shared Function RecentList(ByVal pagenum As Integer) As IDataReader
            Dim qry As New Query(Tables.ClubEvent)
            qry.AddWhere(ClubEvent.Columns.Starttime, Comparison.LessThan, DateTime.Now())
            qry.PageSize = ClubStarterKit.Web.Settings.PageSize()
            qry.PageIndex = pagenum
            qry.OrderBy = OrderBy.Asc(ClubEvent.Columns.Starttime)
            Dim rdr As IDataReader = qry.ExecuteReader()
            Return rdr
            rdr.Close()
        End Function
    End Class
End Namespace